import pymysql
import time
from datetime import datetime
import db_config  # 引入配置文件


def map_order_status(order_status, shipping_status, pay_status):
    """
    映射订单状态、配送状态、支付状态
    """
    # 映射订单状态到 fa_shop_order 的 orderstate
    if order_status in [0, 1, 5, 6]:
        orderstate = 0  # 正常
    elif order_status == 2:
        orderstate = 1  # 已取消
    elif order_status == 3:
        orderstate = 2  # 已失效
    elif order_status == 4:
        orderstate = 4  # 退货退款中
    elif shipping_status == 2:
        orderstate = 3  # 已完成
    else:
        orderstate = 0  # 默认正常

    # 映射配送状态
    shippingstate = 0 if shipping_status == 0 else 1 if shipping_status == 1 else 2

    # 映射支付状态
    paystate = 0 if pay_status == 0 else 1

    return orderstate, shippingstate, paystate


def get_fa_user_id(ecs_user_id, ecs_cursor, fa_cursor):
    """
    获取 FastAdmin 用户表的 user_id，通过 ecs_user_id 查询映射
    """
    # 查询 ECS 用户表中的手机号或其他唯一标识
    ecs_cursor.execute("SELECT mobile_phone FROM ecs_users WHERE user_id = %s", (ecs_user_id,))
    ecs_user = ecs_cursor.fetchone()
    if not ecs_user:
        print(f"Warning: No user found in ecs_users for user_id: {ecs_user_id}")
        return None

    mobile_phone = ecs_user['mobile_phone']

    # 在 FastAdmin 用户表中通过手机号查找 user_id
    fa_cursor.execute("SELECT id FROM fa_user WHERE mobile = %s", (mobile_phone,))
    fa_user = fa_cursor.fetchone()

    if not fa_user:
        print(f"Warning: No user found in fa_user for mobile_phone: {mobile_phone}")
        return None

    return fa_user['id']


def sync_ecs_to_fa_orders():
    try:
        # 使用配置文件中的数据库连接配置
        ecs_conn = pymysql.connect(**db_config.ecs_db_config)
        fa_conn = pymysql.connect(**db_config.fa_db_config)

        ecs_cursor = ecs_conn.cursor(pymysql.cursors.DictCursor)
        fa_cursor = fa_conn.cursor(pymysql.cursors.DictCursor)



        # 查询 ECS 订单信息
        ecs_cursor.execute("SELECT * FROM ecs_order_info")
        ecs_orders = ecs_cursor.fetchall()

        # 遍历 ECS 订单并插入到 FA 表
        for order in ecs_orders:
            # 映射 ECS 用户到 FA 用户
            fa_user_id = get_fa_user_id(order['user_id'], ecs_cursor, fa_cursor)
            if not fa_user_id:
                print(f"Skipping order_sn: {order['order_sn']} due to missing user mapping")
                continue

            # 映射订单状态
            orderstate, shippingstate, paystate = map_order_status(
                order['order_status'], order['shipping_status'], order['pay_status']
            )

            # 构造 FA 订单数据
            fa_order = {
                'order_sn': order['order_sn'],
                'user_id': fa_user_id,  # 使用映射后的 FastAdmin 用户 ID
                'address_id': None,  # 暂无对应字段
                'province_id': order['province'],
                'city_id': order['city'],
                'area_id': order['district'],
                'user_coupon_id': None,  # 暂无对应字段
                'openid': None,  # 暂无对应字段
                'receiver': order['consignee'],
                'address': order['address'],
                'zipcode': order['zipcode'],
                'mobile': order['mobile'],
                'amount': order['goods_amount'] + order['shipping_fee'],
                'discount': order['discount'],
                'shippingfee': order['shipping_fee'],
                'goodsprice': order['goods_amount'],
                'saleamount': order['order_amount'],
                'payamount': order['money_paid'],
                'paytype': order['pay_name'],
                'method': None,  # 暂无对应字段
                'transactionid': None,  # 暂无对应字段
                'expressname': order['shipping_name'],
                'expressno': order['invoice_no'],
                'createtime': order['add_time'],
                'updatetime': int(time.time()),
                'expiretime': None,  # 暂无对应字段
                'paytime': order['pay_time'],
                'refundtime': None,  # 暂无对应字段
                'shippingtime': order['shipping_time'],
                'receivetime': None,  # 暂无对应字段
                'canceltime': None,  # 暂无对应字段
                'deletetime': None,  # 暂无对应字段
                'orderstate': orderstate,
                'shippingstate': shippingstate,
                'paystate': paystate,
                'memo': order['postscript'],
                'status': 'normal'
            }

            # 插入订单到 fa_shop_order 表
            order_query = """
            INSERT INTO fa_shop_order (
                order_sn, user_id, address_id, province_id, city_id, area_id, user_coupon_id, openid, receiver, address,
                zipcode, mobile, amount, discount, shippingfee, goodsprice, saleamount, payamount, paytype, method,
                transactionid, expressname, expressno, createtime, updatetime, expiretime, paytime, refundtime, shippingtime,
                receivetime, canceltime, deletetime, orderstate, shippingstate, paystate, memo, status
            ) VALUES (
                %(order_sn)s, %(user_id)s, %(address_id)s, %(province_id)s, %(city_id)s, %(area_id)s, %(user_coupon_id)s, %(openid)s,
                %(receiver)s, %(address)s, %(zipcode)s, %(mobile)s, %(amount)s, %(discount)s, %(shippingfee)s, %(goodsprice)s,
                %(saleamount)s, %(payamount)s, %(paytype)s, %(method)s, %(transactionid)s, %(expressname)s, %(expressno)s,
                %(createtime)s, %(updatetime)s, %(expiretime)s, %(paytime)s, %(refundtime)s, %(shippingtime)s, %(receivetime)s,
                %(canceltime)s, %(deletetime)s, %(orderstate)s, %(shippingstate)s, %(paystate)s, %(memo)s, %(status)s
            )
            """

            try:
                fa_cursor.execute(order_query, fa_order)
                print(f"Successfully synced order_sn: {order['order_sn']}")
            except Exception as e:
                print(f"Failed to sync order_sn: {order['order_sn']}, error: {e}")

            # 查询该订单的商品信息
            ecs_cursor.execute("SELECT * FROM ecs_order_goods WHERE order_id = %s", (order['order_id'],))
            ecs_order_goods = ecs_cursor.fetchall()

            for goods in ecs_order_goods:
                # 查询 ecs_goods 表获取商品信息
                ecs_cursor.execute("SELECT * FROM ecs_goods WHERE goods_id = %s", (goods['goods_id'],))
                ecs_goods_info = ecs_cursor.fetchone()

                if not ecs_goods_info:
                    print(f"Warning: No matching goods found in ecs_goods for goods_id: {goods['goods_id']}")
                    continue

                # 查询 fa_shop_goods 表获取商品ID
                fa_cursor.execute("SELECT id FROM fa_shop_goods WHERE goods_sn = %s", (ecs_goods_info['goods_sn'],))
                fa_goods_info = fa_cursor.fetchone()

                if not fa_goods_info:
                    print(f"Warning: No matching goods found in fa_shop_goods for goods_sn: {ecs_goods_info['goods_sn']}")
                    continue

                fa_goods_id = fa_goods_info['id']

                # 构造 FA 订单商品数据
                fa_order_goods = {
                    'order_sn': order['order_sn'],
                    'goods_sn': goods['goods_sn'],
                    'goods_id': fa_goods_id,  # 从 fa_shop_goods 中获取的 ID
                    'goods_sku_id': None,  # 暂无对应字段
                    'title': goods['goods_name'],
                    'nums': goods['goods_number'],
                    'marketprice': goods['market_price'],
                    'price': goods['goods_price'],
                    'realprice': goods['goods_price'],
                    'salestate': 0,
                    'commentstate': 0,
                    'attrdata': goods['goods_attr'],
                    'image': ecs_goods_info['goods_thumb'],  # 从 ecs_goods 中获取的缩略图
                    'weight': ecs_goods_info['goods_weight']  # 从 ecs_goods 中获取重量
                }

                # 插入订单商品到 fa_shop_order_goods 表
                goods_query = """
                INSERT INTO fa_shop_order_goods (
                    order_sn, goods_sn, goods_id, goods_sku_id, title, nums, marketprice, price, realprice, salestate,
                    commentstate, attrdata, image, weight
                ) VALUES (
                    %(order_sn)s, %(goods_sn)s, %(goods_id)s, %(goods_sku_id)s, %(title)s, %(nums)s, %(marketprice)s, %(price)s,
                    %(realprice)s, %(salestate)s, %(commentstate)s, %(attrdata)s, %(image)s, %(weight)s
                )
                """

                try:
                    fa_cursor.execute(goods_query, fa_order_goods)
                    print(f"Successfully synced goods_sn: {goods['goods_sn']} for order_sn: {order['order_sn']}")
                except Exception as e:
                    print(f"Failed to sync goods_sn: {goods['goods_sn']} for order_sn: {order['order_sn']}, error: {e}")

            # 插入操作记录到 fa_shop_order_action 表
            action_query = """
            INSERT INTO fa_shop_order_action (
                order_sn, operator, memo, createtime
            ) VALUES (
                %s, %s, %s, %s
            )
            """
            try:
                fa_cursor.execute(action_query, (order['order_sn'], '1', '新网站迁移', int(time.time())))
                print(f"Successfully added action for order_sn: {order['order_sn']}")
            except Exception as e:
                print(f"Failed to add action for order_sn: {order['order_sn']}, error: {e}")

        # 提交事务
        fa_conn.commit()

    except Exception as e:
        print(f"Error: {e}")

    finally:
        # 关闭数据库连接
        if 'ecs_cursor' in locals():
            ecs_cursor.close()
        if 'fa_cursor' in locals():
            fa_cursor.close()
        if 'ecs_conn' in locals():
            ecs_conn.close()
        if 'fa_conn' in locals():
            fa_conn.close()

if __name__ == "__main__":
    sync_ecs_to_fa_orders()
